package subject;

import java.sql.*;
import java.util.*;

import student.DBUtil;

public class SubjectDao {
	// 增
	public int add(Subject st) {
		Connection con = DBUtil.getConnection();
		int i = 0;
		String sql = "insert into s_subject (subjectname,classhour,gradeid) values (?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, st.getSubjectname());
			ps.setLong(2, st.getClasshour());
			ps.setLong(3, st.getGradeid());
			i = ps.executeUpdate();
			System.out.println("增加了" + i + "条");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return i;
	}

	// 删
	public int del(long id) {
		Connection con = DBUtil.getConnection();
		int i = 0;
		String sql = "delete from s_subject where id= " + id + "";
		System.out.println(sql);
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			i = ps.executeUpdate();
			System.out.println("result:" + i);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return i;
	}

	// 改
	public int update(Subject st) {
		Connection con = DBUtil.getConnection();
		int i = 0;
		String sql = "update s_subject set subjectname='" + st.getSubjectname() + "' where id=" + st.getId() + "";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			i = ps.executeUpdate();
			System.out.println("result:" + i);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return i;
	}

	// （查）R:分为两种：1：查单个对象；
	public Subject get(long id) {
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Subject su = null;
		String sql = "select * from s_subject where id=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				su = new Subject();
				su.setId(rs.getLong("id"));
				su.setSubjectname(rs.getString("subjectname"));
				su.setClasshour(rs.getLong("classhour"));
				su.setGradeid(rs.getLong("gradeid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return su;
	}

	// 2:查全部
	public List<Subject> searchAll() {
		String sql = "select * from s_subject order by id asc";

		List<Subject> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Subject su = null;
		try {
			ps = con.prepareStatement(sql);

			rs = ps.executeQuery();
			while (rs.next()) {
				su = new Subject();
				su.setId(rs.getLong("id"));
				su.setSubjectname(rs.getString("subjectname"));
				su.setClasshour(rs.getLong("classhour"));
				su.setGradeid(rs.getLong("gradeid"));

				datas.add(su);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;

	}

	// 分页,count(*)
	/**
	 * 
	 * @param pageSize 页大小
	 * @param pageNo   页号
	 * @return
	 */
	public List<Subject> paging(int pageSize, int pageNo) {

		String sql = "select * from s_subject order by id asc limit ?,? ";

		List<Subject> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Subject su = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, pageSize * (pageNo - 1));
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				su = new Subject();
				su.setId(rs.getLong("id"));
				su.setSubjectname(rs.getString("subjectname"));
				su.setClasshour(rs.getLong("classhour"));
				su.setGradeid(rs.getLong("gradeid"));

				datas.add(su);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;
	}

	private static int PAGE_SIZE = 5;

	public List<Subject> paging(int pageNo) {

		String sql = "SELECT * from s_subject s INNER JOIN s_grade g on(s.gradeid=g.id) order by g.id asc limit ?,? ";
		List<Subject> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Subject su = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, PAGE_SIZE * (pageNo - 1));
			ps.setInt(2, PAGE_SIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				su = new Subject();
				su.setId(rs.getLong("id"));
				su.setSubjectname(rs.getString("subjectname"));
				su.setClasshour(rs.getLong("classhour"));
				// su.setGradeid(rs.getLong("gradeid"));
				su.setGrade(rs.getString("gname"));
				datas.add(su);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;
	}

	// 获得页数
	public int getTotalPage() {
		Connection con = DBUtil.getConnection();
		String sql = "select count(id) from s_subject ";
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				i = rs.getInt("count(id)");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		int page = i / PAGE_SIZE + (i % PAGE_SIZE == 0 ? 0 : 1);
		return page;
	}
}
